﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;

namespace DAL
{
    public class ICAllocationDAL
    {
        /// <summary>
        /// 获取调拨单列表
        /// </summary>
        public DataSet GetICAllocation(string states)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select ROW_NUMBER() OVER (ORDER BY FBrNo)Num,FBrNo, ");
            commandText.Append(" CONVERT(varchar(100), Fdate, 23)Fdate,b.NAME FBillerID ");
            commandText.Append(" from ICAllocation left join sys_master b on b.ID = FBillerID ");
            commandText.Append(" where FStatus =" + states);
            commandText.Append(" order by FBrNo ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取调拨单列表
        /// </summary>
        public DataSet Selissmwc(string FBrNo)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select * from stk_Package where PkgsBarCode in (select PkgsBarCode from ICAllocationDetail where FBrNo='" + FBrNo + "') and PkgsStatus <> 44");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 调拨出未审核明细
        /// </summary>
        public DataSet GetICADetailOut(string FBrNo)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append("   select  b.ItemSku,b.FEntryNo,b.PkgsBarCode,c.HelperCode,a.PlaceBarCode,d.FName,");
            commandText.Append("  (d.FName+a.FName +'-'+  CAST(b.Floor AS nvarchar)) AS PlaceName    ");
            commandText.Append("  from ICAllocationDetail b left join t_StockPlace a on a.FSPID = PosiID ");
            commandText.Append("  left join t_Stock d on a.FParentID=d.FItemID");
            commandText.Append("  left join stk_Package c on c.PkgsBarCode=b.PkgsBarCode  ");
            commandText.Append("  where b.FBrNo ='" + FBrNo + "' ");
            commandText.Append("  and FStatus = 2 and PkgsStatus <> 44  ");

           try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 调拨入未完成明细
        /// </summary>
        public DataSet GetICADetailIn(string FBrNo)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select b.ItemSku,FEntryNo,b.PkgsBarCode,c.HelperCode,a.PlaceBarCode,d.FName,");
            commandText.Append(" (d.FName+a.FName +'-'+  CAST(b.Floor AS nvarchar)) AS PlaceName  ");
            commandText.Append(" from ICAllocationDetail b left join t_StockPlace a on a.FSPID = NPosiID  ");
            commandText.Append("  left join stk_Package c on c.PkgsBarCode=b.PkgsBarCode  ");
            commandText.Append("  left join t_Stock d on a.FParentID=d.FItemID");
            commandText.Append(" where b.FBrNo = '" + FBrNo + "' ");
            commandText.Append(" and FStatus = 3 and c.PkgsStatus =44 ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// 获取库位名称states:0扫描库位的条码与调拨单中条码不一致，states:1扫描库位的条码与调拨单中条码一致，states:3 states = "3";//扫描的库位为空,错误
        /// </summary>
        public string JudgePlaceName(string FBrNo, string PkgsBarCode, string PlaceBarCode, out string PlaceName)
        {
            bool lb_return = false;
            PlaceName = "";
            string states = "";

            DataSet dt = new DataSet();
            StringBuilder sql = new StringBuilder();
           
            //sql.Append("  select a.NPosiID,b.PlaceBarCode,b.FNumber PlaceName from ICAllocationDetail a");
            //sql.Append(" left join  t_StockPlace b on b.FSPID = a.NPosiID  ");
            //sql.Append(" where FBrNo = '" + FBrNo + "'  and  PkgsBarCode ='" + PkgsBarCode + "'");

            sql.Append("	select e.*,f.FNumber PlaceName from ( ");
            sql.Append("     select c.NPosiID,b.PlaceBarCode,c.newPlaceBarCode from ( ");
            sql.Append("      select a.NPosiID, '" + PlaceBarCode + "' newPlaceBarCode from ICAllocationDetail a       ");
            sql.Append("     where FBrNo = '" + FBrNo + "'  and  PkgsBarCode ='" + PkgsBarCode + "') c ");
            sql.Append("     left join t_StockPlace b on b.FSPID = c.NPosiID  )e ");
            sql.Append(" left join t_StockPlace f on f.PlaceBarCode = e.newPlaceBarCode  ");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(sql.ToString());
                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    PlaceName = dr["PlaceName"].ToString().Trim();
                    if (PlaceName != null && PlaceName != "")
                    {
                        string t_PlaceBarCode = dr["PlaceBarCode"].ToString();
                        if (t_PlaceBarCode == PlaceBarCode)
                        {
                            states = "1";//扫描库位的条码与调拨单中条码一致
                        }
                        else
                        {
                            states = "0";//扫描库位的条码与调拨单中条码不一致
                        }
                    }
                    else
                    {
                        states = "3";//扫描的库位名称为空,错误
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return states;
        }

        
        /// <summary>
        /// 盘点调拨明细信息
        /// </summary>
        public bool UpdateICADetail(string FBrNo,string PkgsBarCode, string NStockID, string cwname,string flnor, string states)
        {
            bool lb_return = false;
            DataSet ds = new DataSet();
            DataTable ds1 = new DataTable();
            DataTable ds2 = new DataTable();
            //StringBuilder commandText = new StringBuilder();
            //string NFSPID = "";
            //string NStockID = "";
            //string FloorCon = "";
            List<string> sql = new List<string>();
            if (states == "4")
            {
                //查询仓位id
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                string sqlw = "select FSPID from t_StockPlace where PlaceBarCode='" + cwname + "' and FParentID='" + NStockID + "'";
                ds2 = DbHelperSQL.QueryTable(sqlw);
                if (ds2.Rows.Count == 0)
                {
                    return lb_return = false;
                }
                else
                {
                    sql.Add("update ICAllocationDetail set NStockID='" + NStockID + "',NPosiID='" + ds2.Rows[0]["FSPID"].ToString() + "',NFloor='" + flnor + "' where FBrNo='" + FBrNo + "' and PkgsBarCode='" + PkgsBarCode + "'");
                    sql.Add("update stk_Package set PkgsStatus=0,StockID='" + NStockID + "',PosiID='" + ds2.Rows[0]["FSPID"].ToString() + "',Floor='" + flnor + "' where PkgsBarCode='" + PkgsBarCode + "' and PkgsStatus=44");

                }

            }
            else
            {
                sql.Add("update stk_Package set PkgsStatus=44 where PkgsBarCode='" + PkgsBarCode + "' and PkgsStatus=33");
            }
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                int i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else { lb_return = false; }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            //commandText.Append(" select FParentID NStockID,FSPID NFSPID,FloorCon from t_StockPlace where PlaceBarCode ='" + PlaceBarCode + "'");
            //try
            //{
            //    DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
            //    ds = DbHelperSQL.Query(commandText.ToString());
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            // if (ds != null)
            //{
            //    foreach (DataRow dr in ds.Tables[0].Rows)
            //    {
            //        NStockID = dr["NStockID"].ToString();
            //        NFSPID = dr["NFSPID"].ToString();
            //        FloorCon = dr["FloorCon"].ToString();
            //        if (NFSPID != null && NFSPID != "" && NStockID != null && NStockID != "")
            //        {
            //            lb_return = UpdateICADetailNPosiID(FEntryNo,PkgsBarCode, NStockID, NFSPID,FloorCon, states);
            //        }
            //        else
            //        {
            //            lb_return = false;
            //        }
            //    }            
            //}           
            return lb_return; 
        }

        //向调拨出库单明细中更新库位信息
        public bool UpdateICADetailNPosiID(string FEntryNo,string PkgsBarCode,string NStockID, string NPosiID, string FloorCon, string states)
        {
            bool lb_return = false;
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            string FSPID = "";
            string NFloor = "";
            List<string> sql = new List<string>();

            NFloor = GetFloor(NStockID, NPosiID, FloorCon);

            sql.Add(" update ICAllocationDetail set Fstatus='" + states + @"',NStockID = '" + NStockID + @"',NPosiID='" + NPosiID + @"',NFLOOR='" + NFloor + @"'
            where FEntryNo='" + FEntryNo + @"'");

            sql.Add(" update stk_Package set StockID='"+NStockID+@"',PosiID='"+NPosiID+@"' 
           ,Floor='" + NFloor + @"' where PkgsBarCode='" + PkgsBarCode + @"' ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                int i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else { lb_return = false; }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }

        //获取最大层
        public string GetFloor(string NStockID,string NPosiID, string FloorCon)
        {
            string  Floor = "";
            StringBuilder sql = new StringBuilder();
            DataSet ds = new DataSet();
            if (FloorCon == "2")
            {
                return Floor = "1";
            }
            else
            {
                sql.Append(" select MAX(FLOOR) FLOOR from stk_Package where PkgsStatus in (0,1,88) ");
                sql.Append(" and StockID ='" + NStockID + "' and PosiID ='" + NPosiID + "' ");

                try
                {
                    DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                    ds = DbHelperSQL.Query(sql.ToString());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                if (ds != null)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        Floor = dr[0].ToString().Trim();
                    }
                }
                if (Floor == null || Floor == "")
                {
                    Floor = "1";
                }
                else
                {
                    int maxfloor = 0;
                    maxfloor = int.Parse(Floor) + 1;
                    Floor = maxfloor.ToString();
                }
                return Floor;
            }
           
        }

        /// <summary>
        /// 调拨出  扫码获取明细信息
        /// </summary>
        public bool GetICAOut(string FBrNo, string PkgsBarCode, out string FEntryNo, out string ItemSku, out string PlaceName)
        {
            bool lb_return = false;
            FEntryNo = ""; ItemSku = ""; PlaceName = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select a.FEntryNo,a.ItemSku,(d.FName+b.FName +'-'+  CAST(a.Floor AS nvarchar)) as PlaceName ");
            commandText.Append(" from ICAllocationDetail a left join t_Stock d on a.StockID=d.FItemID ");
            commandText.Append(" left join t_StockPlace b on b.FSPID=a.PosiID ");
            commandText.Append(" where a.FBrNo = '" + FBrNo + "' ");
            commandText.Append(" and a.PkgsBarCode = '" + PkgsBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    FEntryNo = dr[0].ToString().Trim(); if (FEntryNo == null || FEntryNo == "") { return false; }
                    ItemSku = dr[1].ToString().Trim(); if (ItemSku == null || ItemSku == "") { return false; }
                    PlaceName = dr[2].ToString().Trim(); if (PlaceName == null || PlaceName == "") { return false; }
                }
            }

            return lb_return;
        }
        /// <summary>
        /// 调拨入  扫码获取明细信息
        /// </summary>
        public bool GetICAIn(string FBrNo, string PkgsBarCode, out string FEntryNo, out string skname, out string NStockID)
        {
            bool lb_return = false;
            FEntryNo = ""; skname = ""; NStockID = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select a.FEntryNo,b.FName as skname,a.NStockID ");
            commandText.Append(" from ICAllocationDetail a  ");
            commandText.Append(" left join t_Stock b on b.FItemID=a.NStockID ");
            commandText.Append(" where a.FBrNo = '" + FBrNo + "' ");
            commandText.Append(" and a.PkgsBarCode = '" + PkgsBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    FEntryNo = dr[0].ToString().Trim();
                    skname = dr[1].ToString().Trim();
                    NStockID = dr[2].ToString().Trim();
                }
            }

            return lb_return;
        }
        /// <summary>
        /// 查询包装条码在库存状态
        /// </summary>
        public bool GetStkis(string PkgsBarCode, out string Starte)
        {
            bool lb_return = false;
            Starte = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select PkgsStatus from stk_Package where PkgsBarCode = '" + PkgsBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    Starte = dr[0].ToString().Trim();
                   
                }
            }

            return lb_return;
        }
        /// <summary>
        /// 调拨出  审核调拨单
        /// </summary>
        public bool ExamineICA(string fBrNo, string fExamineID)
        {
            bool lb_return = false;
            string fExamineDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            List<string> sql = new List<string>();

            StringBuilder commandText = new StringBuilder();
            commandText.Append(" update ICAllocation set ");
            commandText.Append("  FStatus='3'");
            commandText.Append(" ,FExamineID='" + fExamineID + "'");
            commandText.Append(" ,FExamineDate='" + fExamineDate + "'");
            commandText.Append(" where FBrNo='" + fBrNo + "'");

            sql.Add(commandText.ToString());
            //添加审核明细
            StringBuilder commandD = new StringBuilder();
            commandD.Append(" update ICAllocationDetail set FStatus=3 ");
            commandD.Append(" where FBrNo='" + fBrNo + "'");

            sql.Add(commandD.ToString());
            
            try
            {
                int i = 0;
                i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else
                { lb_return = false; }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }
        /// <summary>
        /// 完成调拨入库同步到金蝶
        /// </summary>
        /// <param name="fBrNo"></param>
        /// <returns></returns>
        public int filishDiaobojd(string fBrNo)
        {
            //调用金蝶接口方法
            WebReference.jdWebService seo = new WebReference.jdWebService();
            int nums = seo.CCPXT_INSERT_ICStockBill("CCPXT_INSERT_DiaoBO", "" + fBrNo + "");
            return nums;
        }
        /// <summary>
        /// 调拨出  完成调拨单
        /// </summary>
        public bool FinishICA(string fBrNo)
        {
            bool lb_return = false;
            DataTable dt = new DataTable();

            List<string> sql = new List<string>();

            sql.Add(" update ICAllocation set FStatus=4 where FBrNo='" + fBrNo + "'");
            sql.Add(" update ICAllocationDetail set FStatus=4 where FBrNo='" + fBrNo + "'");//添加更新明细状态
            //sql.Add("update stk_Package set PkgsStatus=0 where PkgsBarCode in (select PkgsBarCode from ICAllocationDetail where FBrNo='" + fBrNo + "') and PkgsStatus=44");
            //dt = GetICADetailList(fBrNo);
            //if (dt != null)
            //{
            //    for (int i = 0; i < dt.Rows.Count; i++)
            //    {
            //        string PkgsID = dt.Rows[i]["PkgsID"].ToString();
            //        string NStockID = dt.Rows[i]["NStockID"].ToString();
            //        string NPosiID = dt.Rows[i]["NPosiID"].ToString();
            //        string NFloor = dt.Rows[i]["NFloor"].ToString();

            //        StringBuilder commandText = new StringBuilder();
            //        commandText.Append(" update stk_Package set ");
            //        commandText.Append(" StockID=" + NStockID);
            //        commandText.Append(" ,PosiID=" + NPosiID);
            //        commandText.Append(" ,Floor=" + NFloor);
            //        commandText.Append(" where PkgsID=" + PkgsID);

            //        sql.Add(commandText.ToString());
            //    }
            //}

            try
            {
                int i = 0;
                i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else
                { lb_return = false; }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return lb_return;
        }
        public DataTable GetICADetailList(string fBrNo)
        {
            DataTable dt = new DataTable();
            StringBuilder commandText = new StringBuilder();
            try
            {
                commandText.Append(" select FEntryNo,FBrNo,PkgsID,ItemSku,Fname,FModel,Fattr,FGMPBatchNo, ");
                commandText.Append(" FUnit,FQty,StockID,PosiID,Floor,NStockID,NPosiID,NFloor,FNote ");
                commandText.Append(" from ICAllocationDetail ");
                commandText.Append(" where FBrNo ='" + fBrNo + "'");
                dt = DbHelperSQL.QueryTable(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

    }
}
